package co.yixiang.hw.repository;

import co.yixiang.hw.domain.YxStoreCoupon;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import java.util.Map;

/**
* @author Walert
* @date 2020-03-10
*/
public interface YxStoreCouponRepository extends JpaRepository<YxStoreCoupon, Integer>, JpaSpecificationExecutor<YxStoreCoupon> {

    @Query(value = "select " +
            "  t.id                     as id, " +
            "  m1.id                    as cid, " +
            "  t.start_time             as startTime, " +
            "  t.end_time               as endTime, " +
            "  t.total_count            as totalCount, " +
            "  t.remain_count           as remainCount, " +
            "  m1.type                  as type, " +
            "  m1.product_ids           as productIds, " +
            "  m1.title                 as title, " +
            "  m1.integral              as integral, " +
            "  m1.coupon_price          as couponPrice, " +
            "  m1.use_min_price         as useMinPrice, " +
            "  m1.image                 as image, " +
            "  m1.mark                  as mark, " +
            "  if(ISNULL(m2.id),0,1)    as isHad " +
            "from yx_store_coupon_issue t  " +
            "left join yx_store_coupon m1 on (t.cid = m1.id) " +
            "left join yx_store_coupon_user m2 on (m2.issue_coupon_id = t.id and m2.uid = ?1 and m2.status = 0) " +
            "where t.is_del = 0 and m1.is_del = 0 and t.`status` = 1 and t.end_time >= ?2 and t.start_time <= ?2 order by m1.sort asc,m1.add_time desc ",
            countQuery = "select " +
                    "  count(1)" +
                    "from yx_store_coupon_issue t  " +
                    "left join yx_store_coupon m1 on (t.cid = m1.id) " +
                    "left join yx_store_coupon_user m2 on (m2.issue_coupon_id = t.id and m2.uid = ?1 and m2.status = 0) " +
                    "where t.is_del = 0 and m1.is_del = 0 and t.`status` = 1 and t.end_time >= ?2 and t.start_time <= ?2 order by m1.sort asc,m1.add_time desc ",
            nativeQuery = true)
    Page<Map> getCouponList(Integer cid, Integer time, Pageable pageable);
}